﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using QuanLyHocPhi.Model;
using QuanLyHocPhi.BLL;
using QuanLyHocPhi.GUI.DuLieu;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using HorizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment;
namespace QuanLyHocPhi.GUI.TacNghiep
{
    public partial class FormThongKeTheoThoiGian : FormBase
    {
        private DataTable tbb = null;
        private double? tongTien = 0;
        private string fileName = "";
        int pgSize = 5;
        int currenPageIndex = 1;
        int totalPage = 0;
        bool isLockCMBChange = true;
        LoaiHinhDaoTao selectedLoaiHinh = null;
        string namTS = "";
        LopGoc selectedLopGoc = null;
        public FormThongKeTheoThoiGian()
        {
            InitializeComponent();
            colMaPhieuThu.DataPropertyName = "MaPhieuThu";
            colMaSV.DataPropertyName = "MaSinhVien";
            colHHo.DataPropertyName = "HoDem";
            colTen.DataPropertyName = "Ten";
            colNgaySinh.DataPropertyName = "NgaySinh";
            colGioiTinh.DataPropertyName = "GioiTinhStr";
            colLop.DataPropertyName = "LopGoc";
            colLoaiHinh.DataPropertyName = "LoaiHinhDaoTao";
            colNgayNop.DataPropertyName = "NgayNopStr";
            colSoBienLai.DataPropertyName = "SoBienLai";
            colSoTien.DataPropertyName = "SoTienStr";
            colNguoiThu.DataPropertyName = "NguoiThu";
            gridPhieuThu.AllowUserToAddRows = false;
            gridPhieuThu.AllowUserToDeleteRows = false;
            gridPhieuThu.AutoGenerateColumns = false;
            gridPhieuThu.ReadOnly = true;
            //this.WindowState = FormWindowState.Maximized;
            fileName = string.Format("{0}\\TEMPLATES\\MauThongKeTheoNgay.xls",
                Application.StartupPath);
            hienThiDanhSachNguoiThu();
            hienThiDanhSachLoaiHinh();
        }

        private void InitComboSuggestion(ComboBox cmb, object datasource, string displayMember, string valueMember, string[] rangeDisplay)
        {
            cmb.AutoCompleteSource = AutoCompleteSource.CustomSource;
            cmb.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
            cmb.DataSource = datasource;
            if (displayMember != null)
                cmb.DisplayMember = displayMember;
            if (valueMember != null)
                cmb.ValueMember = valueMember;
            // Xác định AutoCompleteCustomeSource
            var autoCompletSet = new AutoCompleteStringCollection();
            autoCompletSet.AddRange(rangeDisplay);
            cmb.AutoCompleteCustomSource = autoCompletSet;

            // Các tinh chỉnh nâng cao
            cmb.Leave += (oSender, oE) =>
            {
                var cbx = oSender as ComboBox;
                if (cbx == null)
                    return;
                cbx.BackColor = cbx.SelectedItem == null ? Color.LightPink : SystemColors.Window;
            };

            cmb.KeyDown += (oSender, oE) =>
            {
                if (oE.KeyCode != Keys.Enter)
                    return;
                var cbx = oSender as ComboBox;
                if (cbx == null)
                    return;
                cbx.BackColor = cbx.SelectedItem == null ? Color.LightPink : SystemColors.Window;
            };
        }

        /// <summary>
        /// Hiển thị danh sách người thu tiền
        /// </summary>
        void hienThiDanhSachNguoiThu()
        {
            var ds = BizNguoiSuDung.GetListNguoiSuDung();
            ds.Insert(0, new NguoiSuDung() { HoTen = "Tất cả", TenDangNhap = "-1000" });
            InitComboSuggestion(cmbNguoiThu, ds, "HoTen", "TenDangNhap", ds.Select(e => e.HoTen).ToArray());
            //cmbNguoiThu.DataSource = ds;
            //cmbNguoiThu.DisplayMember = "HoTen";
            //cmbNguoiThu.ValueMember = "TenDangNhap";
        }

        /// <summary>
        /// Hiển thị danh sách loại hình đào tạo
        /// </summary>
        void hienThiDanhSachLoaiHinh()
        {
            isLockCMBChange = true;
            var ds = BizLoaiHinhDaoTao.GetListLoaiHinh();
            ds.Insert(0, new LoaiHinhDaoTao() { TenLoaiHinh = "Tất cả", MaLoaiHinh = "-1000" });
            InitComboSuggestion(cmbLoaiHinh, ds, "TenLoaiHinh", "MaLoaiHinh", ds.Select(e => e.TenLoaiHinh).ToArray());
            //cmbLoaiHinh.DataSource = ds;
            //cmbLoaiHinh.DisplayMember = "TenLoaiHinh";
            //cmbLoaiHinh.ValueMember = "MaLoaiHinh";
            isLockCMBChange = false;
            cmbLoaiHinh_SelectedIndexChanged(this, new EventArgs());
        }

        /// <summary>
        /// Hiển thị danh sách năm tuyển sinh theo loại hình đào tạo
        /// </summary>
        /// <param name="maLoaiHinh"></param>
        void hienThiDanhSachNamTS(string maLoaiHinh)
        {
            isLockCMBChange = true;
            cmbNamTuyenSinh.DataSource = null;
            cmbLop.DataSource = null;
            var ds = BizLopGoc.GetListNamTuyenSinh(maLoaiHinh);
            ds.Insert(0, "Tất cả");
            InitComboSuggestion(cmbNamTuyenSinh, ds, null, null, ds.ToArray());
            //cmbNamTuyenSinh.DataSource = ds;
            isLockCMBChange = false;
            cmbNamTuyenSinh_SelectedIndexChanged(this, new EventArgs());
        }

        /// <summary>
        /// Hiển thị danh sách lớp theo loại hình đào tạo và năm tuyển sinh
        /// </summary>
        /// <param name="maLoaiHinh"></param>
        /// <param name="namTuyenSinh"></param>
        void hienThiDanhSachLop(string maLoaiHinh, string namTuyenSinh)
        {
            var ds = BizLopGoc.GetListLopGoc(maLoaiHinh, namTuyenSinh);
            ds.Insert(0, new LopGoc() { TenLopGoc = "Tất cả", MaLopGoc = "-1000" });
            InitComboSuggestion(cmbLop, ds, "TenLopGoc", "MaLopGoc", ds.Select(e => e.TenLopGoc).ToArray());
            //cmbLop.DataSource = ds;
            //cmbLop.DisplayMember = "TenLopGoc";
            //cmbLop.ValueMember = "MaLopGoc";
        }

        private void bntThongKe_Click(object sender, EventArgs e)
        {
            currenPageIndex = 1;
            thongKe();
        }

        /// <summary>
        /// Xử lý việc thống kê trên cửa sổ
        /// </summary>
        private void thongKe()
        {
            #region Tiền xử lý
            bool ngayLoi = dateFrom.Value.Date > dateTo.Value.Date ? true : false;

            if (ngayLoi)
            {
                MessageBox.Show("Thời gian nhập vào không hợp lệ", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            pgSize = (int)numericUpDown1.Value;
            var dFrom = dateFrom.Value.Date;
            var tempDateFrom = dateTo.Value.Date;
            var dTo = new DateTime(tempDateFrom.Year, tempDateFrom.Month, tempDateFrom.Day, 23, 59, 59);
            var nguoiThu = (NguoiSuDung)cmbNguoiThu.SelectedItem;
            selectedLoaiHinh = (LoaiHinhDaoTao)cmbLoaiHinh.SelectedItem;
            namTS = cmbNamTuyenSinh.SelectedIndex == 0 ? "-1000" : cmbNamTuyenSinh.SelectedValue.ToString();
            selectedLopGoc = (LopGoc)cmbLop.SelectedItem;
            tongTien = 0;
            int? soBanGhi = 0;
            var danhSach = new List<ThongKePhieuThu>();
            #endregion

            FormProgress frm = new FormProgress();
            frm.DoWork += delegate(object oSender, DoWorkEventArgs oE)
            {
                BizPhieuThu.GetThongKe(chkThoiGian.Checked, dFrom, dTo, nguoiThu, selectedLoaiHinh, namTS, selectedLopGoc, out soBanGhi, out tongTien);
                tinhTongTrang(soBanGhi.Value);
                danhSach = BizPhieuThu.GetThongKe(chkThoiGian.Checked, dFrom, dTo, nguoiThu, selectedLoaiHinh, namTS, selectedLopGoc, pgSize, currenPageIndex);
            };
            frm.RunWorkerCompleted += delegate(object oSender, RunWorkerCompletedEventArgs oE)
            {
                if (oE.Error != null)
                {
                    string msg = oE.Error.Message;
                    if (oE.Error.InnerException != null)
                        msg += oE.Error.InnerException.Message;
                    DialogUtils.ShowError(msg);
                }

            };
            frm.StartWorking(this);
            lbTrangTooltip.Text = string.Format("{0}/{1}", currenPageIndex, totalPage);
            lbTongTienToolTip.Text = tongTien.ToString().FormatCurrency() + " đồng";
            bdPhieuThu.DataSource = danhSach;
            gridPhieuThu.DataSource = bdPhieuThu;
        }

        /// <summary>
        /// Tổng số trang
        /// </summary>
        /// <param name="soBanGhi"></param>
        private void tinhTongTrang(int soBanGhi)
        {
            totalPage = soBanGhi / pgSize;
            if (soBanGhi % pgSize != 0)
                totalPage++;
        }

        private void bdPhieuThu_DataSourceChanged(object sender, EventArgs e)
        {
            var tb = bdPhieuThu.DataSource as List<ThongKePhieuThu>;
            if (tb.Count == 0)
            {
                bntCuoi.Enabled = false;
                bntTiep.Enabled = false;
                bntTruoc.Enabled = false;
                bntDauTooltip.Enabled = false;
                bntXuatFile.Enabled = false;
            }
            else
            {
                bntCuoi.Enabled = true;
                bntTiep.Enabled = true;
                bntTruoc.Enabled = true;
                bntDauTooltip.Enabled = true;
                bntXuatFile.Enabled = true;
            }
        }

        private void cbNguoiThu_SelectedIndexChanged(object sender, EventArgs e)
        {
            //Không gì cả
        }

        private void dateFrom_ValueChanged(object sender, EventArgs e)
        {
            dateTo.Value = dateFrom.Value;
        }

        private void cmbLoaiHinh_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (!isLockCMBChange)
            {
                hienThiDanhSachNamTS(cmbLoaiHinh.SelectedValue.ToString());
            }
        }

        private void cmbNamTuyenSinh_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (!isLockCMBChange)
            {
                hienThiDanhSachLop(cmbLoaiHinh.SelectedValue.ToString(), cmbNamTuyenSinh.SelectedValue.ToString());
            }
        }

        private void bntCuoi_Click(object sender, EventArgs e)
        {
            if (currenPageIndex != totalPage)
            {
                currenPageIndex = totalPage;
                thongKe();
            }
        }

        private void bntTiep_Click(object sender, EventArgs e)
        {
            if (currenPageIndex < totalPage)
            {
                currenPageIndex++;
                thongKe();
            }
        }

        private void bntTruoc_Click(object sender, EventArgs e)
        {
            if (currenPageIndex > 1)
            {
                currenPageIndex--;
                thongKe();
            }
        }

        private void bntDauTooltip_Click(object sender, EventArgs e)
        {
            if (currenPageIndex != 1)
            {
                currenPageIndex = 1;
                thongKe();
            }
        }

        private void bntXuatFile_Click(object sender, EventArgs e)
        {
            int tongColumn = 8;
            var danhSach = bdPhieuThu.DataSource as List<ThongKePhieuThu>;
            var soLuong = danhSach.Count;
            bool duLieuRong = soLuong == 0 ? true : false;
            var selectedNguoiThu = cmbNguoiThu.SelectedItem as NguoiSuDung;
            if (duLieuRong)
                MessageBox.Show("Hiện tại danh sách là rỗng", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            else
            {
                #region Tiền xử lý
                //Thiết lập tên file mẫu
                if (selectedLoaiHinh != null && selectedLoaiHinh.MaLoaiHinh != "-1000")
                {
                    if (selectedLopGoc != null && selectedLopGoc.MaLopGoc != "-1000")
                    {
                        fileName = string.Format("{0}\\TEMPLATES\\MauThongKeTheoLop.xls",
        Application.StartupPath);
                        tongColumn = 8;
                    }
                    else
                    {
                        fileName = string.Format("{0}\\TEMPLATES\\MauThongKeTheoLoaiHinh.xls",
        Application.StartupPath);
                        tongColumn = 9;
                    }
                }
                else
                {
                    fileName = string.Format("{0}\\TEMPLATES\\MauThongKeTheoThoiGian.xls",
        Application.StartupPath);
                    tongColumn = 10;
                }


                if (!File.Exists(fileName))
                {
                    MessageBox.Show("File mẫu không tồn tại", "Lỗi");
                    return;
                }
                FileStream fs = null;
                try
                {
                    fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                }
                catch (Exception)
                {
                    MessageBox.Show("File mẫu đang mở. Vui lòng đóng lại trước khi tiếp tục", "Lỗi");
                    return;
                }
                #endregion

                #region Làm việc với file excel
                FormProgress frm = new FormProgress();
                frm.DoWork += delegate(object oSender, DoWorkEventArgs oE)
                {
                    HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true);

                    #region Tạo style cho thông tin cs nhân
                    var titleCellStyle = templateWorkbook.CreateCellStyle();
                    titleCellStyle.Alignment = HorizontalAlignment.CENTER;
                    var titleFont = templateWorkbook.CreateFont();
                    titleFont.Boldweight = (short)FontBoldWeight.BOLD;
                    titleFont.FontHeight = 236;
                    titleFont.FontName = "Times New Roman";
                    titleCellStyle.SetFont(titleFont);
                    #endregion

                    #region Tạo các style cho phần hiển thị nội dung
                    var detailCellStyle = templateWorkbook.CreateCellStyle();
                    detailCellStyle.Alignment = HorizontalAlignment.CENTER;
                    detailCellStyle.BorderBottom = CellBorderType.DASHED;
                    detailCellStyle.BorderLeft = CellBorderType.THIN;
                    detailCellStyle.BorderRight = CellBorderType.THIN;
                    var cellHoStyle = templateWorkbook.CreateCellStyle();
                    cellHoStyle.BorderBottom = CellBorderType.DASHED;
                    cellHoStyle.BorderLeft = CellBorderType.THIN;
                    var cellTenStyle = templateWorkbook.CreateCellStyle();
                    cellTenStyle.BorderBottom = CellBorderType.DASHED;
                    cellTenStyle.BorderRight = CellBorderType.THIN;

                    var detailCellStyleEnd = templateWorkbook.CreateCellStyle();
                    detailCellStyleEnd.Alignment = HorizontalAlignment.CENTER;
                    detailCellStyleEnd.BorderBottom = CellBorderType.THIN;
                    detailCellStyleEnd.BorderLeft = CellBorderType.THIN;
                    detailCellStyleEnd.BorderRight = CellBorderType.THIN;
                    var cellHoStyleEnd = templateWorkbook.CreateCellStyle();
                    cellHoStyleEnd.BorderBottom = CellBorderType.THIN;
                    cellHoStyleEnd.BorderLeft = CellBorderType.THIN;
                    var cellTenStyleEnd = templateWorkbook.CreateCellStyle();
                    cellTenStyleEnd.BorderBottom = CellBorderType.THIN;
                    cellTenStyleEnd.BorderRight = CellBorderType.THIN;

                    var cellTongSoStyle = templateWorkbook.CreateCellStyle();
                    var fontCellTongSo = templateWorkbook.CreateFont();
                    fontCellTongSo.IsItalic = true;
                    cellTongSoStyle.SetFont(fontCellTongSo);
                    var cellBottomStyle = templateWorkbook.CreateCellStyle();
                    cellBottomStyle.Alignment = HorizontalAlignment.CENTER;
                    var fontCellBottomStyle = templateWorkbook.CreateFont();
                    fontCellBottomStyle.Boldweight = (short)FontBoldWeight.BOLD;
                    cellBottomStyle.SetFont(fontCellBottomStyle);
                    #endregion

                    #region Khởi tạo
                    var sheet = templateWorkbook.GetSheet("sheet1");
                    var indexRow = 5;
                    var row = sheet.GetRow(indexRow);
                    #endregion

                    #region Chèn phần thông tin cá nhân
                    Cell cell;
                    if (chkThoiGian.Checked)
                    {
                        //Có kèm yếu tố thời gian
                        try
                        {
                            cell = row.CreateCell(2);
                        }
                        catch
                        {
                            cell = row.GetCell(2);
                        }
                        cell.SetCellValue(string.Format("{0:dd/MM/yyyy}", dateFrom.Value));
                        cell.CellStyle = titleCellStyle;
                        try
                        {
                            cell = row.CreateCell(4);
                        }
                        catch
                        {
                            cell = row.GetCell(4);
                        }
                        cell.SetCellValue(string.Format("{0:dd/MM/yyyy}", dateTo.Value));
                        cell.CellStyle = titleCellStyle;

                        if (selectedLoaiHinh != null && selectedLoaiHinh.MaLoaiHinh != "-1000")
                        {
                            indexRow++; row = sheet.GetRow(indexRow);

                            if (selectedLopGoc != null && selectedLopGoc.MaLopGoc != "-1000")
                            {
                                try
                                {
                                    cell = row.CreateCell(2);
                                }
                                catch
                                {
                                    cell = row.GetCell(2);
                                }
                                cell.SetCellValue(selectedLoaiHinh.TenLoaiHinh);
                                cell.CellStyle = titleCellStyle;

                                try
                                {
                                    cell = row.CreateCell(4);
                                }
                                catch
                                {
                                    cell = row.GetCell(4);
                                }
                                cell.SetCellValue(selectedLopGoc.TenLopGoc);
                                cell.CellStyle = titleCellStyle;
                            }
                            else
                            {
                                try
                                {
                                    cell = row.CreateCell(2);
                                }
                                catch
                                {
                                    cell = row.GetCell(2);
                                }
                                cell.SetCellValue(selectedLoaiHinh.TenLoaiHinh);
                                cell.CellStyle = titleCellStyle;
                            }
                        }
                    }
                    else
                    {
                        try
                        {
                            cell = row.GetCell(1);
                            cell.SetCellValue("");
                            cell = row.GetCell(3);
                            cell.SetCellValue("");
                        }
                        catch (Exception)
                        {
                        }
                    }
                    #endregion

                    #region Chèn nội dung danh sách sinh viên

                    for (int j = 0; j < soLuong; j++)
                    {
                        var dtRow = danhSach[j];
                        row = sheet.CreateRow(j + 8);
                        cell = row.CreateCell(0);
                        cell.SetCellValue(j + 1);
                        cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;

                        cell = row.CreateCell(1);
                        cell.SetCellValue(dtRow.MaSinhVien);
                        cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;

                        cell = row.CreateCell(2);
                        cell.SetCellValue(dtRow.HoDem);
                        cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;

                        cell = row.CreateCell(3);
                        cell.SetCellValue(dtRow.Ten);
                        cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;

                        cell = row.CreateCell(4);
                        cell.SetCellValue(dtRow.NgaySinh);
                        cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;

                        cell = row.CreateCell(5);
                        cell.SetCellValue(dtRow.GioiTinhStr);
                        cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;



                        if (selectedLoaiHinh != null && selectedLoaiHinh.MaLoaiHinh != "-1000")
                        {
                            if (selectedLopGoc != null && selectedLopGoc.MaLopGoc != "-1000")
                            {
                                cell = row.CreateCell(6);
                                cell.SetCellValue(dtRow.NgayNopStr);
                                cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;

                                cell = row.CreateCell(7);
                                cell.SetCellValue(dtRow.SoBienLai);
                                cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;

                                cell = row.CreateCell(8);
                                cell.SetCellValue(dtRow.SoTien);
                                cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;
                            }
                            else
                            {
                                cell = row.CreateCell(6);
                                cell.SetCellValue(dtRow.LopGoc);
                                cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;

                                cell = row.CreateCell(7);
                                cell.SetCellValue(dtRow.NgayNopStr);
                                cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;

                                cell = row.CreateCell(8);
                                cell.SetCellValue(dtRow.SoBienLai);
                                cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;

                                cell = row.CreateCell(9);
                                cell.SetCellValue(dtRow.SoTien);
                                cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;
                            }
                        }
                        else
                        {
                            cell = row.CreateCell(6);
                            cell.SetCellValue(dtRow.LopGoc);
                            cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;

                            cell = row.CreateCell(7);
                            cell.SetCellValue(dtRow.LoaiHinhDaoTao);
                            cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;

                            cell = row.CreateCell(8);
                            cell.SetCellValue(dtRow.NgayNopStr);
                            cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;

                            cell = row.CreateCell(9);
                            cell.SetCellValue(dtRow.SoBienLai);
                            cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;

                            cell = row.CreateCell(10);
                            cell.SetCellValue(dtRow.SoTien);
                            cell.CellStyle = (j == (soLuong - 1)) ? detailCellStyleEnd : detailCellStyle;
                        }
                    }
                    #endregion

                    #region Chèn phần bottom
                    indexRow = soLuong + 8;
                    row = sheet.CreateRow(indexRow);
                    try
                    {
                        cell = row.CreateCell(tongColumn - 1);
                    }
                    catch (Exception)
                    {

                        cell = row.GetCell(tongColumn - 1);
                    }
                    cell.SetCellValue("Tổng");
                    cell.CellStyle = titleCellStyle;

                    try
                    {
                        cell = row.CreateCell(tongColumn);
                    }
                    catch (Exception)
                    {

                        cell = row.GetCell(tongColumn);
                    }
                    cell.SetCellValue(tongTien != null ? tongTien.Value.ToString().FormatCurrency() : "");
                    cell.CellStyle = titleCellStyle;

                    indexRow++;
                    row = sheet.GetRow(indexRow);
                    try
                    {
                        cell = row.CreateCell(1);
                    }
                    catch (Exception)
                    {

                        cell = row.GetCell(1);
                    }
                    cell.SetCellValue("Bằng chữ: ");
                    cell.CellStyle = cellTongSoStyle;
                    try
                    {
                        cell = row.CreateCell(2);
                    }
                    catch (Exception)
                    {

                        cell = row.GetCell(2);
                    }
                    cell.SetCellValue(tongTien.ToString().ConvertNum2Text() + " đồng");
                    cell.CellStyle = cellTongSoStyle;

                    indexRow++;
                    row = sheet.GetRow(indexRow);
                    try
                    {
                        cell = row.CreateCell(7);
                    }
                    catch (Exception)
                    {

                        cell = row.GetCell(7);
                    }
                    cell.SetCellValue(string.Format("Huế, ngày {0:dd} tháng {0:MM} năm {0:yyyy}", DateTime.Now));
                    cell.CellStyle = cellTongSoStyle;

                    indexRow++;
                    row = sheet.GetRow(indexRow);

                    if (selectedNguoiThu.TenDangNhap != "-1000")
                    {
                        try
                        {
                            cell = row.CreateCell(1);
                        }
                        catch (Exception)
                        {

                            cell = row.GetCell(1);
                        }
                        cell.SetCellValue(string.Format("Người thu"));
                        cell.CellStyle = titleCellStyle;
                    }
                    try
                    {
                        cell = row.CreateCell(4);
                    }
                    catch (Exception)
                    {

                        cell = row.GetCell(4);
                    }
                    cell.SetCellValue(string.Format("Kế toán trưởng"));
                    cell.CellStyle = titleCellStyle;
                    try
                    {
                        cell = row.CreateCell(8);
                    }
                    catch (Exception)
                    {

                        cell = row.GetCell(8);
                    }
                    cell.SetCellValue(string.Format("Trưởng đơn vị"));
                    cell.CellStyle = titleCellStyle;

                    indexRow += 6;
                    row = sheet.CreateRow(indexRow);
                    if (selectedNguoiThu.TenDangNhap != "-1000")
                    {
                        try
                        {
                            cell = row.CreateCell(1);
                        }
                        catch (Exception)
                        {

                            cell = row.GetCell(1);
                        }
                        cell.SetCellValue(selectedNguoiThu.HoTen);
                        cell.CellStyle = cellBottomStyle;
                    }
                    var thongtin = BizNguoiSuDung.ThongTinDangKy();
                    if (thongtin != null && thongtin.Length == 2)
                    {
                        try
                        {
                            cell = row.CreateCell(4);
                        }
                        catch (Exception)
                        {

                            cell = row.GetCell(4);
                        }
                        cell.SetCellValue(thongtin[0]);
                        cell.CellStyle = cellBottomStyle;

                        try
                        {
                            cell = row.CreateCell(8);
                        }
                        catch (Exception)
                        {

                            cell = row.GetCell(8);
                        }
                        cell.SetCellValue(thongtin[1]);
                        cell.CellStyle = cellBottomStyle;
                    }
                    #endregion

                    #region Lưu ra file Excel mới
                    sheet.ForceFormulaRecalculation = true;
                    if (!Directory.Exists(string.Format("{0}\\Temp", Application.StartupPath)))
                        Directory.CreateDirectory(string.Format("{0}\\Temp", Application.StartupPath));
                    var tenfile = string.Format("{0}\\Temp\\{1:dd_MM_yyyy_hh_mm_ss}.xls", Application.StartupPath, DateTime.Now);
                    try
                    {
                        using (var fileData = new FileStream(tenfile, FileMode.Create))
                        {
                            templateWorkbook.Write(fileData);
                            fileData.Close();

                            #region Excel commented
                            #endregion
                            System.Diagnostics.Process.Start(tenfile);
                        }
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.ToString());
                    }
                    fs.Close();
                    #endregion
                };
                #endregion

                #region On complete
                frm.RunWorkerCompleted += delegate(object oSender, RunWorkerCompletedEventArgs oE)
                {
                    if (oE.Error != null)
                    {
                        string msg = oE.Error.Message;
                        if (oE.Error.InnerException != null)
                            msg += oE.Error.InnerException.Message;
                        DialogUtils.ShowError(msg);
                    }

                };
                #endregion

                frm.StartWorking(this);
            }
        }
    }
}
